package com.api.gbhg.liuwenbo.version2.api;

import com.alibaba.fastjson.JSON;
import com.api.gbhg.liuwenbo.version2.pojo.BudgetSectionIncome2;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import weaver.conn.RecordSet;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.*;

/**
 * 财政拨款2
 */
@Path("gb/FinancialAppropriation2ExportApi")
public class FinancialAppropriation2ExportApi {

    private int thisIndex = 3;
    private HSSFCellStyle fontStyle = null;//正常字体大小靠右垂直居中
    private HSSFCellStyle fontCenterStyle = null;//字体大小跟居中
    private HSSFCellStyle cellStyle = null;//字体大小跟居中以及居中



    //人员经费
//    private static final String[] RYJF = {"2010901","2041001","2080501","2080505","2080506","2101101",
//            "2101103","2210201","2210203","2010903","2010950"};
    private static Map<String,Object> ryjfMap ;
    //公用经费
//    private static final String[] GYJF = {"2010901","2041001","2080501","2010903","2010950"};
    private static Map<String,Object> gyjfMap ;
    //项目经费
//    private static final String[] XMJF = {"2010902","2010905","2010907","2010908","2010909","2010910",
//            "2010911","2010912","2041002","2041006","2041007"};
    private static Map<String,Object> xmjfMap ;
    private static Object[][] xmjfArr;
    static {
        ryjfMap = new HashMap<>();
        ryjfMap.put("2010901","行政运行-人员经费");
        ryjfMap.put("2041001","缉私警察－行政运行-人员经费");
        ryjfMap.put("2080501","行政单位离退休");
        ryjfMap.put("2080505","机关事业单位基本养老保险缴费支出");
        ryjfMap.put("2080506","机关事业单位职业年金缴费支出");
        ryjfMap.put("2101101","行政单位医疗");
        ryjfMap.put("2101103","公务员医疗补助");
        ryjfMap.put("2210201","住房公积金");
        ryjfMap.put("2210203","购房补贴");
        ryjfMap.put("2010903","机关服务");
        ryjfMap.put("2010950","事业运行");

        gyjfMap = new HashMap<>();
        gyjfMap.put("2010901","行政运行-公用经费");
        gyjfMap.put("2041001","缉私警察－行政运行-公用经费");
        gyjfMap.put("2080501","行政单位离退休");
        gyjfMap.put("2010903","机关服务");
        gyjfMap.put("2010950","事业运行");

        xmjfMap = new HashMap<>();
        Map<String,Object> tempMap1 = new HashMap<>();
        xmjfMap.put("2010902","一般行政管理事务");
        xmjfMap.put("2010905","缉私办案");
        xmjfMap.put("2010902","一般行政管理事务");
        xmjfMap.put("2010902","一般行政管理事务");
        xmjfMap.put("2010902","一般行政管理事务");
        xmjfMap.put("2010902","一般行政管理事务");

        xmjfArr = new Object[][]{
                {"2010902", "一般行政管理事务", Arrays.asList("海关综合业务管理专项", "海关国际交流与合作", "中央基建投资")},
                {"2010905", "缉私办案", "海关缉私办案费"},
                {"2010907", "口岸管理", "中国电子口岸建设运行经费"},
                {"2010908", "信息化建设", "信息化运行维护（直接合并这个所有的科目下级）"},
                {"2010909", "海关关务", Arrays.asList("海关执法保障及能力提升专项", "海关系统基建支出", "资产运行维护")},
                {"2010910", "关税征管", "海关关税征管业务费"},
                {"2010911", "海关监管", "海关监管专项"},
                {"2010912", "检验检疫", "海关检验检疫专项"},
                {"2041002", "一般行政管理事务", "资产运行维护"},
                {"2041006", "信息化建设", "信息化运行维护"},
                {"2041007", "缉私业务", "打击走私犯罪业务费"}};
    }

    private Map<String,Object> msgMap ;

    @POST
    @Path("/exportExcel")
    @Produces(MediaType.APPLICATION_JSON)
    public String exportExcel(@Context HttpServletResponse response,Map<String,Object> param){
        msgMap = new HashMap<>();

        if (!param.containsKey("nf") || !param.containsKey("jz")){
            msgMap.put("code",204);
            msgMap.put("msg","非法参数！");
            msgMap.put("data","");
            return JSON.toJSONString(msgMap);
        }
        return downLoadExcel(response,param.get("nf").toString(),param.get("jz").toString());
    }

    private String downLoadExcel(HttpServletResponse response,String nf,String jz)  {
        Map<String, Object> data = getData(nf, jz);

        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("财政拨款2");

            //新增数据行，并且设置单元格数据
            HSSFRow row = sheet.createRow(0);
            //定义单元格风格
            cellStyle = workbook.createCellStyle();
            //设置水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置字体
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short)11);
            //设置是否为斜体
            font.setItalic(false);
            //设置是否加粗
            font.setBold(true);

            //设置字体颜色
            font.setColor(IndexedColors.BLACK.index);
            //设置行的高度
            cellStyle.setFont(font);
            row.setHeightInPoints(20);
            //设置默认的列宽度
            sheet.setDefaultColumnWidth(9);
            sheet.setColumnWidth(0,500*10);
            sheet.setColumnWidth(1,500*10);
            sheet.setColumnWidth(2,700*10);
            sheet.setColumnWidth(3,1200*10);
            sheet.setColumnWidth(4,500*10);
            sheet.setColumnWidth(5,500*10);
            sheet.setColumnWidth(6,550*10);
            sheet.setColumnWidth(7,500*10);
            sheet.setColumnWidth(8,500*10);
            sheet.setColumnWidth(9,500*10);
            sheet.setColumnWidth(10,500*10);
            sheet.setColumnWidth(11,500*10);
            sheet.setColumnWidth(12,500*10);
            sheet.setColumnWidth(13,500*10);
            sheet.setColumnWidth(14,500*10);
            sheet.setColumnWidth(15,500*10);
            sheet.setColumnWidth(16,600*10);
            sheet.setColumnWidth(17,600*10);
            sheet.setColumnWidth(18,500*10);
            sheet.setColumnWidth(19,500*10);
            sheet.setColumnWidth(20,500*10);
            sheet.setColumnWidth(21,500*10);

            //设置背景颜色的style
            HSSFCellStyle coloTitleStyle = workbook.createCellStyle();
            coloTitleStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
            coloTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            coloTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            //设置字段style
            HSSFFont fontTitle = workbook.createFont();
            fontTitle.setFontHeightInPoints((short)11);
            coloTitleStyle.setFont(fontTitle);

            //创建字体大小无居中style----------------
            fontStyle = workbook.createCellStyle();
            fontStyle.setFont(fontTitle);
            fontStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            //创建字体大小水平居中style----------------
            fontCenterStyle = workbook.createCellStyle();
            fontCenterStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            fontCenterStyle.setAlignment(HorizontalAlignment.CENTER);
            fontCenterStyle.setFont(fontTitle);

            //合并 四个参数（分别为起始行，结束行，起始列，结束列）
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 7);
            sheet.addMergedRegion(region);
            //添加excel表头第一行
            HSSFCell cellTl = row.createCell(0);
            cellTl.setCellStyle(cellStyle);
            HSSFRichTextString title = new HSSFRichTextString(nf+"年财政拨款部门预算收支情况表");
            cellTl.setCellValue(title);

            HSSFRow rowbt1 = sheet.createRow(1);
            HSSFCell cellNf = rowbt1.createCell(0);
            cellNf.setCellStyle(cellStyle);
            HSSFRichTextString nfCell = new HSSFRichTextString("统计日期:"+ LocalDate.now().getMonthValue()+"月"+LocalDate.now().getDayOfMonth());
            cellNf.setCellValue(nfCell);
            //--------设置列标题----------------------------------
            String[] headers = {"类别","科目编码","科目名称","上年结转","本年收入","合计","上年结转预算安排","本年收入预算安排","合计","上年结转未安排",
                    "本年收入未安排","合计","上年结转预算支出","本年收入预算支出","支出合计","上年结转预算执行率","本年收入预算执行率","预算执行率合计"};

            HSSFRow rowbt2 = sheet.createRow(2);
            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell ;
                if(i == 3){
                    cell = rowbt2.createCell(i+1);
                }else if(i == 4){
                    //下标已超前两位所以需要+2
                    cell = rowbt2.createCell(i+2);
                }else if(i>4){
                    //下标已超前三位所以需要+3
                    cell = rowbt2.createCell(i+3);
                }else{
                    cell = rowbt2.createCell(i);
                }
                cell.setCellStyle(cellStyle);
                if (i==2 ||i==3 || i== 4){
                    //这里是为了处理两列合并
                    int j ;
                    if (i==3) {
                        j = 4;
                    }else if(i == 4) {
                        j = 6;
                    }else {
                        j = i;
                    }

                    rowbt2.createCell(j+1);
                    cell.setCellValue(headers[i]);
                    CellRangeAddress tmpregion = new CellRangeAddress(2, 2, j, j+1);
                    sheet.addMergedRegion(tmpregion);
                }else{
                    cell.setCellValue(headers[i]);
                }
            }

            List<Integer> hjIndex = new ArrayList<>();
            //------------开始写入数据1 人员经费
            int startIx = thisIndex+1;//此下标作为起始下标用 用作于求和起始位置 以及合并行起始位置
            List<BudgetSectionIncome2> ryjfData = (List<BudgetSectionIncome2>)data.get("0");
            for (int i = 0; i < ryjfData.size(); i++) {
                BudgetSectionIncome2 bean = ryjfData.get(i);
                HSSFRow tempRow = sheet.createRow(thisIndex);
                HSSFCell cell = tempRow.createCell(0);

                tempRow.createCell(1).setCellValue(bean.getName());

                tempRow.createCell(2).setCellValue(bean.getKmmc());
                tempRow.createCell(3);
                //合并科目名称
                sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 2, 3));

                tempRow.createCell(4).setCellValue(Double.parseDouble(bean.getSnjz()));
                tempRow.createCell(5);
                //合并行上年
                sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 4, 5));

                tempRow.createCell(6).setCellValue(Double.parseDouble(bean.getBnsr()));
                tempRow.createCell(7);
                //合并行本年
                sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 6, 7));

                tempRow.createCell(8).setCellValue(Double.parseDouble(bean.getHj()));

                tempRow.createCell(9).setCellValue(Double.parseDouble(bean.getSnjzysap()));

                tempRow.createCell(10).setCellValue(Double.parseDouble(bean.getBnsrysap()));

                tempRow.createCell(11).setCellValue(Double.parseDouble(bean.getHj1()));

                tempRow.createCell(12).setCellValue(Double.parseDouble(bean.getSnjzwap()));

                tempRow.createCell(13).setCellValue(Double.parseDouble(bean.getBnsrwap()));

                tempRow.createCell(14).setCellValue(Double.parseDouble(bean.getHj2()));

                tempRow.createCell(15).setCellValue(Double.parseDouble(bean.getSnjzyszc()));

                tempRow.createCell(16).setCellValue(Double.parseDouble(bean.getBnsryszc()));

                tempRow.createCell(17).setCellValue(Double.parseDouble(bean.getHj3()));

                tempRow.createCell(18).setCellValue(Double.parseDouble(bean.getSnjzyszxl()));

                tempRow.createCell(19).setCellValue(Double.parseDouble(bean.getBnsryszxl()));

                tempRow.createCell(20).setCellValue(Double.parseDouble(bean.getHj4()));

                thisIndex++;

                //设置表格中字体
                for (int k = 0; k < 19; k++) {
                    if (k == 2)
                        tempRow.getCell(k).setCellStyle(fontStyle);
                    else
                        tempRow.getCell(k).setCellStyle(fontCenterStyle);
                }
            }
            //设置人员合计
            hjIndex.add(thisIndex);
            HSSFRow ryhjRow = sheet.createRow(thisIndex);
            ryhjRow.createCell(0);
            ryhjRow.createCell(1).setCellValue("合计(人员)");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 1, 3));
            ryhjRow.createCell(4).setCellFormula("SUM(E"+startIx+":F"+thisIndex+")");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 4, 5));
            ryhjRow.createCell(6).setCellFormula("SUM(G"+startIx+":H"+thisIndex+")");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 6, 7));
            ryhjRow.createCell(8).setCellFormula("SUM(I"+startIx+":I"+thisIndex+")");
            ryhjRow.createCell(9).setCellFormula("SUM(J"+startIx+":J"+thisIndex+")");
            ryhjRow.createCell(10).setCellFormula("SUM(K"+startIx+":K"+thisIndex+")");
            ryhjRow.createCell(11).setCellFormula("SUM(L"+startIx+":L"+thisIndex+")");
            ryhjRow.createCell(12).setCellFormula("SUM(M"+startIx+":M"+thisIndex+")");
            ryhjRow.createCell(13).setCellFormula("SUM(N"+startIx+":N"+thisIndex+")");
            ryhjRow.createCell(14).setCellFormula("SUM(O"+startIx+":O"+thisIndex+")");
            ryhjRow.createCell(15).setCellFormula("SUM(P"+startIx+":P"+thisIndex+")");
            ryhjRow.createCell(16).setCellFormula("SUM(Q"+startIx+":Q"+thisIndex+")");
            ryhjRow.createCell(17).setCellFormula("SUM(R"+startIx+":R"+thisIndex+")");
            ryhjRow.createCell(18).setCellFormula("SUM(S"+startIx+":S"+thisIndex+")");
            ryhjRow.createCell(19).setCellFormula("SUM(T"+startIx+":T"+thisIndex+")");
            ryhjRow.createCell(20).setCellFormula("SUM(U"+startIx+":U"+thisIndex+")");

            sheet.getRow(thisIndex-ryjfData.size()).getCell(0).setCellValue("人员经费");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex-ryjfData.size(), thisIndex, 0, 0));

            thisIndex++;

            //---------------设置公用经费
            startIx = thisIndex+1;
            List<BudgetSectionIncome2> gyjfData = (List<BudgetSectionIncome2>)data.get("1");
            for (int i = 0; i < gyjfData.size(); i++) {
                BudgetSectionIncome2 bean = gyjfData.get(i);
                HSSFRow tempRow = sheet.createRow(thisIndex);
                HSSFCell cell = tempRow.createCell(0);

                tempRow.createCell(1).setCellValue(bean.getName());

                tempRow.createCell(2).setCellValue(bean.getKmmc());
                tempRow.createCell(3);
                //合并科目名称
                sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 2, 3));

                tempRow.createCell(4).setCellValue(Double.parseDouble(bean.getSnjz()));
                tempRow.createCell(5);
                //合并行上年
                sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 4, 5));

                tempRow.createCell(6).setCellValue(Double.parseDouble(bean.getBnsr()));
                tempRow.createCell(7);
                //合并行本年
                sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 6, 7));

                tempRow.createCell(8).setCellValue(Double.parseDouble(bean.getHj()));

                tempRow.createCell(9).setCellValue(Double.parseDouble(bean.getSnjzysap()));

                tempRow.createCell(10).setCellValue(Double.parseDouble(bean.getBnsrysap()));

                tempRow.createCell(11).setCellValue(Double.parseDouble(bean.getHj1()));

                tempRow.createCell(12).setCellValue(Double.parseDouble(bean.getSnjzwap()));

                tempRow.createCell(13).setCellValue(Double.parseDouble(bean.getBnsrwap()));

                tempRow.createCell(14).setCellValue(Double.parseDouble(bean.getHj2()));

                tempRow.createCell(15).setCellValue(Double.parseDouble(bean.getSnjzyszc()));

                tempRow.createCell(16).setCellValue(Double.parseDouble(bean.getBnsryszc()));

                tempRow.createCell(17).setCellValue(Double.parseDouble(bean.getHj3()));

                tempRow.createCell(18).setCellValue(Double.parseDouble(bean.getSnjzyszxl()));

                tempRow.createCell(19).setCellValue(Double.parseDouble(bean.getBnsryszxl()));

                tempRow.createCell(20).setCellValue(Double.parseDouble(bean.getHj4()));

                thisIndex++;
                //设置表格中字体
                for (int k = 0; k < 19; k++) {
                    if (k == 2)
                        tempRow.getCell(k).setCellStyle(fontStyle);
                    else
                        tempRow.getCell(k).setCellStyle(fontCenterStyle);
                }
            }
            //-----------设置公用经费合计
            hjIndex.add(thisIndex);
            HSSFRow gyhjRow = sheet.createRow(thisIndex);
            gyhjRow.createCell(0);
            gyhjRow.createCell(1).setCellValue("合计(公用)");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 1, 3));
            gyhjRow.createCell(4).setCellFormula("SUM(E"+startIx+":F"+thisIndex+")");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 4, 5));
            gyhjRow.createCell(6).setCellFormula("SUM(G"+startIx+":H"+thisIndex+")");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 6, 7));
            gyhjRow.createCell(8).setCellFormula("SUM(I"+startIx+":I"+thisIndex+")");
            gyhjRow.createCell(9).setCellFormula("SUM(J"+startIx+":J"+thisIndex+")");
            gyhjRow.createCell(10).setCellFormula("SUM(K"+startIx+":K"+thisIndex+")");
            gyhjRow.createCell(11).setCellFormula("SUM(L"+startIx+":L"+thisIndex+")");
            gyhjRow.createCell(12).setCellFormula("SUM(M"+startIx+":M"+thisIndex+")");
            gyhjRow.createCell(13).setCellFormula("SUM(N"+startIx+":N"+thisIndex+")");
            gyhjRow.createCell(14).setCellFormula("SUM(O"+startIx+":O"+thisIndex+")");
            gyhjRow.createCell(15).setCellFormula("SUM(P"+startIx+":P"+thisIndex+")");
            gyhjRow.createCell(16).setCellFormula("SUM(Q"+startIx+":Q"+thisIndex+")");
            gyhjRow.createCell(17).setCellFormula("SUM(R"+startIx+":R"+thisIndex+")");
            gyhjRow.createCell(18).setCellFormula("SUM(S"+startIx+":S"+thisIndex+")");
            gyhjRow.createCell(19).setCellFormula("SUM(T"+startIx+":T"+thisIndex+")");
            gyhjRow.createCell(20).setCellFormula("SUM(U"+startIx+":U"+thisIndex+")");


            sheet.getRow(thisIndex-gyjfData.size()).getCell(0).setCellValue("公用经费");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex-gyjfData.size(), thisIndex, 0, 0));

            thisIndex++;
            //---------------设置项目经费
            startIx = thisIndex+1;
            List<BudgetSectionIncome2> xmjfData = (List<BudgetSectionIncome2>)data.get("2");
            for (int i = 0; i < xmjfData.size(); i++) {
                BudgetSectionIncome2 bean = xmjfData.get(i);
                if ((bean.getName().equals("2010902") && bean.getXj().size()>0) || (bean.getName().equals("2010909") && bean.getXj().size()>0)){
                    List<BudgetSectionIncome2> xjList = bean.getXj();
                    for (int j = 0; j < xjList.size(); j++) {
                        BudgetSectionIncome2 bean2 = xjList.get(j);
                        HSSFRow tempRow = sheet.createRow(thisIndex);
                        tempRow.createCell(0);

                        tempRow.createCell(1);
                        tempRow.createCell(2);

                        tempRow.createCell(3).setCellValue(bean2.getKmmc());

                        tempRow.createCell(4).setCellValue(Double.parseDouble(bean2.getSnjz()));
                        tempRow.createCell(5);
                        //合并行上年
                        sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 4, 5));

                        tempRow.createCell(6).setCellValue(Double.parseDouble(bean2.getBnsr()));
                        tempRow.createCell(7);
                        //合并行本年
                        sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 6, 7));

                        tempRow.createCell(8).setCellValue(Double.parseDouble(bean2.getHj()));

                        tempRow.createCell(9).setCellValue(Double.parseDouble(bean2.getSnjzysap()));

                        tempRow.createCell(10).setCellValue(Double.parseDouble(bean2.getBnsrysap()));

                        tempRow.createCell(11).setCellValue(Double.parseDouble(bean2.getHj1()));

                        tempRow.createCell(12).setCellValue(Double.parseDouble(bean2.getSnjzwap()));

                        tempRow.createCell(13).setCellValue(Double.parseDouble(bean2.getBnsrwap()));

                        tempRow.createCell(14).setCellValue(Double.parseDouble(bean2.getHj2()));

                        tempRow.createCell(15).setCellValue(Double.parseDouble(bean2.getSnjzyszc()));

                        tempRow.createCell(16).setCellValue(Double.parseDouble(bean2.getBnsryszc()));

                        tempRow.createCell(17).setCellValue(Double.parseDouble(bean2.getHj3()));

                        tempRow.createCell(18).setCellValue(Double.parseDouble(bean2.getSnjzyszxl()));

                        tempRow.createCell(19).setCellValue(Double.parseDouble(bean2.getBnsryszxl()));

                        tempRow.createCell(20).setCellValue(Double.parseDouble(bean2.getHj4()));
                        thisIndex++;
                        //设置表格中字体
                        for (int k = 0; k < 19; k++) {
                            if (k == 2 || k == 3)
                                tempRow.getCell(k).setCellStyle(fontStyle);
                            else
                                tempRow.getCell(k).setCellStyle(fontCenterStyle);
                        }
                    }
                    sheet.getRow(thisIndex-xjList.size()).getCell(2).setCellValue(bean.getKmmc());
                    sheet.getRow(thisIndex-xjList.size()).getCell(1).setCellValue(bean.getName());
                    sheet.addMergedRegion(new CellRangeAddress(thisIndex-xjList.size(), thisIndex-1, 2, 2));
                    sheet.addMergedRegion(new CellRangeAddress(thisIndex-xjList.size(), thisIndex-1, 1, 1));

                }else if(!bean.getName().equals("2010902") && !bean.getName().equals("2010909")) {
                    HSSFRow tempRow = sheet.createRow(thisIndex);
                    HSSFCell cell = tempRow.createCell(0);

                    tempRow.createCell(1).setCellValue(bean.getName());

                    tempRow.createCell(2).setCellValue(bean.getKmmc().split("-")[0]);
                    tempRow.createCell(3).setCellValue(bean.getKmmc().split("-")[1]);

                    tempRow.createCell(4).setCellValue(Double.parseDouble(bean.getSnjz()));
                    tempRow.createCell(5);
                    //合并行上年
                    sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 4, 5));

                    tempRow.createCell(6).setCellValue(Double.parseDouble(bean.getBnsr()));
                    tempRow.createCell(7);
                    //合并行本年
                    sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 6, 7));

                    tempRow.createCell(8).setCellValue(Double.parseDouble(bean.getHj()));

                    tempRow.createCell(9).setCellValue(Double.parseDouble(bean.getSnjzysap()));

                    tempRow.createCell(10).setCellValue(Double.parseDouble(bean.getBnsrysap()));

                    tempRow.createCell(11).setCellValue(Double.parseDouble(bean.getHj1()));

                    tempRow.createCell(12).setCellValue(Double.parseDouble(bean.getSnjzwap()));

                    tempRow.createCell(13).setCellValue(Double.parseDouble(bean.getBnsrwap()));

                    tempRow.createCell(14).setCellValue(Double.parseDouble(bean.getHj2()));

                    tempRow.createCell(15).setCellValue(Double.parseDouble(bean.getSnjzyszc()));

                    tempRow.createCell(16).setCellValue(Double.parseDouble(bean.getBnsryszc()));

                    tempRow.createCell(17).setCellValue(Double.parseDouble(bean.getHj3()));

                    tempRow.createCell(18).setCellValue(Double.parseDouble(bean.getSnjzyszxl()));

                    tempRow.createCell(19).setCellValue(Double.parseDouble(bean.getBnsryszxl()));

                    tempRow.createCell(20).setCellValue(Double.parseDouble(bean.getHj4()));
                    thisIndex++;
                    //设置表格中字体
                    for (int k = 0; k < 19; k++) {
                        if (k == 2 || k == 3)
                            tempRow.getCell(k).setCellStyle(fontStyle);
                        else
                            tempRow.getCell(k).setCellStyle(fontCenterStyle);
                    }
                }

            }
            //-----------设置公用经费合计
            hjIndex.add(thisIndex);
            HSSFRow xmhjRow = sheet.createRow(thisIndex);
            xmhjRow.createCell(0);
            xmhjRow.createCell(1).setCellValue("合计(项目)");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 1, 3));
            xmhjRow.createCell(4).setCellFormula("SUM(E"+startIx+":F"+thisIndex+")");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 4, 5));
            xmhjRow.createCell(6).setCellFormula("SUM(G"+startIx+":H"+thisIndex+")");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 6, 7));
            xmhjRow.createCell(8).setCellFormula("SUM(I"+startIx+":I"+thisIndex+")");
            xmhjRow.createCell(9).setCellFormula("SUM(J"+startIx+":J"+thisIndex+")");
            xmhjRow.createCell(10).setCellFormula("SUM(K"+startIx+":K"+thisIndex+")");
            xmhjRow.createCell(11).setCellFormula("SUM(L"+startIx+":L"+thisIndex+")");
            xmhjRow.createCell(12).setCellFormula("SUM(M"+startIx+":M"+thisIndex+")");
            xmhjRow.createCell(13).setCellFormula("SUM(N"+startIx+":N"+thisIndex+")");
            xmhjRow.createCell(14).setCellFormula("SUM(O"+startIx+":O"+thisIndex+")");
            xmhjRow.createCell(15).setCellFormula("SUM(P"+startIx+":P"+thisIndex+")");
            xmhjRow.createCell(16).setCellFormula("SUM(Q"+startIx+":Q"+thisIndex+")");
            xmhjRow.createCell(17).setCellFormula("SUM(R"+startIx+":R"+thisIndex+")");
            xmhjRow.createCell(18).setCellFormula("SUM(S"+startIx+":S"+thisIndex+")");
            xmhjRow.createCell(19).setCellFormula("SUM(T"+startIx+":T"+thisIndex+")");
            xmhjRow.createCell(20).setCellFormula("SUM(U"+startIx+":U"+thisIndex+")");

            //由于startIx赋值时+1用于对应函数行位置 所以在此需-1对应表行位置
            sheet.getRow(startIx-1).getCell(0).setCellValue("项目经费");
            sheet.addMergedRegion(new CellRangeAddress(startIx-1, thisIndex, 0, 0));

            //设置合计字体样式
            for (Integer ix:hjIndex) {
                HSSFRow hjRow = sheet.getRow(ix);
                for (int h = 1; h < 19; h++) {
                    if (h==2 || h == 3 || h == 5 || h == 7)
                        continue;
                    HSSFCell cellhj = hjRow.getCell(h);
                    cellhj.setCellStyle(cellStyle);
                }
            }
            thisIndex++;
            //------------设置总计
            startIx = thisIndex+1;
            HSSFRow zjRow = sheet.createRow(thisIndex);
            zjRow.createCell(0).setCellValue("合计(项目)");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 0, 3));
            zjRow.createCell(4).setCellFormula("SUM(E"+(hjIndex.get(0)+1)+",E"+(hjIndex.get(1)+1)+",E"+(hjIndex.get(2)+1)+")");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 4, 5));
            zjRow.createCell(6).setCellFormula("SUM(G"+(hjIndex.get(0)+1)+",G"+(hjIndex.get(1)+1)+",G"+(hjIndex.get(2)+1)+")");
            sheet.addMergedRegion(new CellRangeAddress(thisIndex, thisIndex, 6, 7));
            zjRow.createCell(8).setCellFormula("SUM(I"+(hjIndex.get(0)+1)+",I"+(hjIndex.get(1)+1)+",I"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(9).setCellFormula("SUM(J"+(hjIndex.get(0)+1)+",J"+(hjIndex.get(1)+1)+",J"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(10).setCellFormula("SUM(K"+(hjIndex.get(0)+1)+",K"+(hjIndex.get(1)+1)+",K"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(11).setCellFormula("SUM(L"+(hjIndex.get(0)+1)+",L"+(hjIndex.get(1)+1)+",L"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(12).setCellFormula("SUM(M"+(hjIndex.get(0)+1)+",M"+(hjIndex.get(1)+1)+",M"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(13).setCellFormula("SUM(N"+(hjIndex.get(0)+1)+",N"+(hjIndex.get(1)+1)+",N"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(14).setCellFormula("SUM(O"+(hjIndex.get(0)+1)+",O"+(hjIndex.get(1)+1)+",O"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(15).setCellFormula("SUM(P"+(hjIndex.get(0)+1)+",P"+(hjIndex.get(1)+1)+",P"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(16).setCellFormula("SUM(Q"+(hjIndex.get(0)+1)+",Q"+(hjIndex.get(1)+1)+",Q"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(17).setCellFormula("SUM(R"+(hjIndex.get(0)+1)+",R"+(hjIndex.get(1)+1)+",R"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(18).setCellFormula("SUM(S"+(hjIndex.get(0)+1)+",S"+(hjIndex.get(1)+1)+",S"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(19).setCellFormula("SUM(T"+(hjIndex.get(0)+1)+",T"+(hjIndex.get(1)+1)+",T"+(hjIndex.get(2)+1)+")");
            zjRow.createCell(20).setCellFormula("SUM(U"+(hjIndex.get(0)+1)+",U"+(hjIndex.get(1)+1)+",U"+(hjIndex.get(2)+1)+")");

            //设置样式
            for (int h = 0; h < 19; h++) {
                if (h==1 || h==2 || h == 3 || h == 5 || h == 7)
                    continue;
                HSSFCell cellhj = zjRow.getCell(h);
                cellhj.setCellStyle(cellStyle);
            }

            //设置要导出的文件的名字
            String downFilename  = "财政拨款2"  + ".xls";
            //服务器发给浏览器的数据是使用UTF-8编码
            response.setContentType("application/octet-stream ; charset=ISO-8859-1 ");

            //设置浏览器的编码为UTF-8
            response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(downFilename,"UTF-8"));

            response.flushBuffer();
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();

            msgMap.put("code","200");
            msgMap.put("msg","");
            msgMap.put("data","");

        } catch (Exception e) {
            e.printStackTrace();
        }


        return JSON.toJSONString(msgMap);
    }


    public Map<String,Object> getData(String nf,String jz){

        Map<String,Object> map = new HashMap<>();


        List<BudgetSectionIncome2> ryjfList = new LinkedList<>();
        for (Map.Entry<String, Object> entry : ryjfMap.entrySet()) {
            BudgetSectionIncome2 ryjf = getRyjfData(nf, jz,entry.getKey(),0);
            ryjfList.add(ryjf);
        }
        map.put("0",ryjfList);

        List<BudgetSectionIncome2> gyjfList = new LinkedList<>();
        for (Map.Entry<String, Object> entry : gyjfMap.entrySet()) {
            BudgetSectionIncome2 gyjf = getRyjfData(nf, jz,entry.getKey(),0);
            gyjfList.add(gyjf);
        }
        map.put("1",gyjfList);

        List<BudgetSectionIncome2> xmjfList = new LinkedList<>();
        for (int i = 0; i < xmjfArr.length; i++) {
            BudgetSectionIncome2 xmjf = getXmjfData(nf, jz, xmjfArr[i]);
            xmjfList.add(xmjf);
        }
        map.put("2",xmjfList);
        return map;
    }

    public BudgetSectionIncome2 getRyjfData(String nf,String jz,String id,Integer srzxId){
        BudgetSectionIncome2 bean = getBudgetSectionIncome2();
        bean.setKmmc(ryjfMap.get(id).toString());
        bean.setName(id);
        //srzx 0:人员经费 1:公用经费 2:项目经费
        String srzx = "";
        if (srzxId==0){
            srzx = "人员经费";
        }else if(srzxId==1){
            srzx = "公用经费";
        }

        RecordSet rs = new RecordSet();
        String sql = "select * from (select id,substring(gnkmmc,2,9) gnkmmc,kmdm,"+jz+",yjsr,snjzhpje,yhpje,yqndyjjzje,dw from uf_zyczbksr_km where srnf = "+nf+")kmb where gnkmmc in ("+ id +")";
        rs.executeQuery(sql);
        while (rs.next()){

            //接下来需要累加最末级金额
            List<BudgetSectionIncome2> xjList = getCzbksrEjData(rs.getString("id"), jz,rs.getString("dw"),nf,srzx);
            if (xjList.size()>0){
                BigDecimal snjz = BigDecimal.ZERO;//上年结转
                BigDecimal bnsr = BigDecimal.ZERO;//本年收入
                BigDecimal snjzysap = BigDecimal.ZERO;//上年结转预算安排
                BigDecimal bnsrysap = BigDecimal.ZERO;//本年收入预算安排
                BigDecimal snjzwap = BigDecimal.ZERO; //上年结转未安排
                BigDecimal bnsrwap = BigDecimal.ZERO; //本年收入未安排
                BigDecimal snjzyszc = BigDecimal.ZERO; //上年结转预算支出
                BigDecimal bnsryszc = BigDecimal.ZERO; //本年收入预算支出
                BigDecimal snjzyszxl = BigDecimal.ZERO; //上年结转预算执行率
                BigDecimal bnsryszxl = BigDecimal.ZERO; //本年收入预算执行率
                for (BudgetSectionIncome2 beans:xjList){
                    snjz = snjz.add(new BigDecimal(beans.getSnjz()));
                    bnsr = bnsr.add(new BigDecimal(beans.getBnsr()));

                    snjzysap = snjzysap.add(new BigDecimal(beans.getSnjzysap()));
                    bnsrysap = bnsrysap.add(new BigDecimal(beans.getBnsrysap()));

                    snjzwap = snjzwap.add(new BigDecimal(beans.getSnjzwap()));
                    bnsrwap = bnsrwap.add(new BigDecimal(beans.getBnsrwap()));

                    snjzyszc = snjzyszc.add(new BigDecimal(beans.getSnjzyszc()));
                    bnsryszc = bnsryszc.add(new BigDecimal(beans.getBnsryszc()));

                    snjzyszxl = snjzyszxl.add(new BigDecimal(beans.getSnjzyszxl()));
                    bnsryszxl = bnsryszxl.add(new BigDecimal(beans.getBnsryszxl()));
                }
                bean.setSnjz(new BigDecimal(bean.getSnjz()).add(snjz).toString());
                bean.setBnsr(new BigDecimal(bean.getBnsr()).add(bnsr).toString());
                bean.setHj(String.valueOf(new BigDecimal(bean.getSnjz()).add(new BigDecimal(bean.getBnsr()))));

                bean.setSnjzysap(new BigDecimal(bean.getSnjzysap()).add(snjzysap).toString());
                bean.setBnsrysap(new BigDecimal(bean.getBnsrysap()).add(bnsrysap).toString());
                bean.setHj1(String.valueOf(new BigDecimal(bean.getSnjzysap()).add(new BigDecimal(bean.getBnsrysap()))));

                bean.setSnjzwap(new BigDecimal(bean.getSnjzwap()).add(snjzwap).toString());
                bean.setBnsrwap(new BigDecimal(bean.getBnsrwap()).add(bnsrwap).toString());
                bean.setHj2(String.valueOf(new BigDecimal(bean.getSnjzwap()).add(new BigDecimal(bean.getBnsrwap()))));

                bean.setSnjzyszc(new BigDecimal(bean.getSnjzyszc()).add(snjzyszc).toString());
                bean.setBnsryszc(new BigDecimal(bean.getBnsryszc()).add(bnsryszc).toString());
                bean.setHj3(String.valueOf(new BigDecimal(bean.getSnjzyszc()).add(new BigDecimal(bean.getBnsryszc()))));

                bean.setSnjzyszxl(new BigDecimal(bean.getSnjzyszxl()).add(snjzyszxl).toString());
                bean.setBnsryszxl(new BigDecimal(bean.getBnsryszxl()).add(bnsryszxl).toString());
                bean.setHj4(String.valueOf(new BigDecimal(bean.getSnjzyszxl()).add(new BigDecimal(bean.getBnsryszxl()))));
            }else{
                //已到最下级  取自身值
                String snjz = new BigDecimal(bean.getSnjz()).add(new BigDecimal(rs.getString(jz))).toString();
                bean.setSnjz(snjz);
                String yjsr = new BigDecimal(bean.getSnjz()).add(new BigDecimal(rs.getString("yjsr"))).toString();
                bean.setBnsr(yjsr);
                bean.setHj(String.valueOf(new BigDecimal(snjz).add(new BigDecimal(yjsr))));

                bean.setSnjzysap(new BigDecimal(bean.getSnjzysap()).add(new BigDecimal(rs.getString("snjzhpje"))).toString());
                bean.setBnsrysap(new BigDecimal(bean.getBnsrysap()).add(new BigDecimal(rs.getString("yhpje"))).toString());
                bean.setHj1(String.valueOf(new BigDecimal(bean.getSnjzysap()).add(new BigDecimal(bean.getBnsrysap()))));

                bean.setSnjzwap(new BigDecimal(bean.getSnjzwap()).add(new BigDecimal(rs.getString("yqndyjjzje"))).toString());
                bean.setBnsrwap(new BigDecimal(bean.getBnsrwap()).add(new BigDecimal(rs.getString("yjsr"))).toString());
                bean.setHj2(String.valueOf(new BigDecimal(bean.getSnjzwap()).add(new BigDecimal(bean.getBnsrwap()))));

                String snbxje = getDfcz_tybxje("0", "1", "0", rs.getString("id"), rs.getString("dw"), nf);
                bean.setSnjzyszc(new BigDecimal(bean.getSnjzyszc()).add(new BigDecimal(snbxje)).toString());
                String bnbxje = getDfcz_tybxje("0", "0", "0", rs.getString("id"), rs.getString("dw"), nf);
                bean.setBnsryszc(new BigDecimal(bean.getBnsryszc()).add(new BigDecimal(bnbxje)).toString());
                bean.setHj3(String.valueOf(new BigDecimal(bean.getSnjzyszc()).add(new BigDecimal(bean.getBnsryszc()))));

                BigDecimal snzxl = new BigDecimal("0");
                if (new BigDecimal(bean.getSnjzysap()).compareTo(new BigDecimal("0"))>0)
                    snzxl = new BigDecimal(bean.getSnjzyszc()).divide(new BigDecimal(bean.getSnjzysap()), 2, BigDecimal.ROUND_HALF_UP);
                bean.setSnjzyszxl(new BigDecimal(bean.getSnjzyszxl()).add(snzxl).toString());

                BigDecimal bnzxl = new BigDecimal("0");
                if (new BigDecimal(bean.getBnsrysap()).compareTo(new BigDecimal("0"))>0)
                    bnzxl = new BigDecimal(bean.getBnsryszc()).divide(new BigDecimal(bean.getBnsrysap()), 2, BigDecimal.ROUND_HALF_UP);
                bean.setBnsryszxl(new BigDecimal(bean.getBnsryszxl()).add(bnzxl).toString());
                bean.setHj4(String.valueOf(snzxl.add(bnzxl)));
            }
        }
        return bean;
    }

    /**
     * 获取中央科目二级数据
     * @param kmId
     * @return
     */
    public List<BudgetSectionIncome2> getCzbksrEjData(String kmId, String jzField,String dw,String nf,String srzx){
        RecordSet rs = new RecordSet();
        String sql = "select id,srzx,"+jzField+",yjsr,snjzhpje,yhpje,yqndyjjzje,yjsr from uf_zyczbksr_xj where szgnkm = "+kmId+" and (sjzxsr is null or sjzxsr='')  ";
        if (!srzx.isEmpty()){
            sql +=" and srzx = '"+srzx+"' ";
        }
        rs.executeQuery(sql);
        List<BudgetSectionIncome2> list = new ArrayList<>();

        while (rs.next()){
            BudgetSectionIncome2  bean = getBudgetSectionIncome2();
            bean.setId(rs.getString("id"));
            bean.setDataid(rs.getString("id"));
            bean.setKmjxj(1);
            bean.setKmmc(rs.getString("srzx"));
            List<BudgetSectionIncome2> xjList = recursionXj(rs.getString("id"), jzField,dw,nf);
            if (xjList.size()>0){
                BigDecimal snjz = BigDecimal.ZERO;//上年结转
                BigDecimal bnsr = BigDecimal.ZERO;//本年收入
                BigDecimal snjzysap = BigDecimal.ZERO;//上年结转预算安排
                BigDecimal bnsrysap = BigDecimal.ZERO;//本年收入预算安排
                BigDecimal snjzwap = BigDecimal.ZERO; //上年结转未安排
                BigDecimal bnsrwap = BigDecimal.ZERO; //本年收入未安排
                BigDecimal snjzyszc = BigDecimal.ZERO; //上年结转预算支出
                BigDecimal bnsryszc = BigDecimal.ZERO; //本年收入预算支出
                BigDecimal snjzyszxl = BigDecimal.ZERO; //上年结转预算执行率
                BigDecimal bnsryszxl = BigDecimal.ZERO; //本年收入预算执行率
                for (BudgetSectionIncome2 beans:xjList){
                    snjz = snjz.add(new BigDecimal(beans.getSnjz()));
                    bnsr = bnsr.add(new BigDecimal(beans.getBnsr()));

                    snjzysap = snjzysap.add(new BigDecimal(beans.getSnjzysap()));
                    bnsrysap = bnsrysap.add(new BigDecimal(beans.getBnsrysap()));

                    snjzwap = snjzwap.add(new BigDecimal(beans.getSnjzwap()));
                    bnsrwap = bnsrwap.add(new BigDecimal(beans.getBnsrwap()));

                    snjzyszc = snjzyszc.add(new BigDecimal(beans.getSnjzyszc()));
                    bnsryszc = bnsryszc.add(new BigDecimal(beans.getBnsryszc()));

                    snjzyszxl = snjzyszxl.add(new BigDecimal(beans.getSnjzyszxl()));
                    bnsryszxl = bnsryszxl.add(new BigDecimal(beans.getBnsryszxl()));
                }
                bean.setSnjz(snjz.toString());
                bean.setBnsr(bnsr.toString());
                bean.setHj(snjz.add(bnsr).toString());

                bean.setSnjzysap(snjzysap.toString());
                bean.setBnsrysap(bnsrysap.toString());
                bean.setHj1(snjzysap.add(bnsrysap).toString());

                bean.setSnjzwap(snjzwap.toString());
                bean.setBnsrwap(bnsrwap.toString());
                bean.setHj2(snjzwap.add(bnsrwap).toString());

                bean.setSnjzyszc(snjzyszc.toString());
                bean.setBnsryszc(bnsryszc.toString());
                bean.setHj3(snjzyszc.add(bnsryszc).toString());

                bean.setSnjzyszxl(snjzyszxl.toString());
                bean.setBnsryszxl(bnsryszxl.toString());
                bean.setHj4(snjzyszxl.add(bnsryszxl).toString());
            }else{
                //已到最下级  取自身值
                String snjz = new BigDecimal(bean.getSnjz()).add(new BigDecimal(rs.getString(jzField))).toString();
                bean.setSnjz(snjz);
                String yjsr = new BigDecimal(bean.getSnjz()).add(new BigDecimal(rs.getString("yjsr"))).toString();
                bean.setBnsr(yjsr);
                bean.setHj(String.valueOf(new BigDecimal(snjz).add(new BigDecimal(yjsr))));

                bean.setSnjzysap(new BigDecimal(bean.getSnjzysap()).add(new BigDecimal(rs.getString("snjzhpje"))).toString());
                bean.setBnsrysap(new BigDecimal(bean.getBnsrysap()).add(new BigDecimal(rs.getString("yhpje"))).toString());
                bean.setHj1(String.valueOf(new BigDecimal(bean.getSnjzysap()).add(new BigDecimal(bean.getBnsrysap()))));

                bean.setSnjzwap(new BigDecimal(bean.getSnjzwap()).add(new BigDecimal(rs.getString("yqndyjjzje"))).toString());
                bean.setBnsrwap(new BigDecimal(bean.getBnsrwap()).add(new BigDecimal(rs.getString("yjsr"))).toString());
                bean.setHj2(String.valueOf(new BigDecimal(bean.getSnjzwap()).add(new BigDecimal(bean.getBnsrwap()))));

                String snbxje = getDfcz_tybxje("0", "1", "0", rs.getString("id"), rs.getString("dw"), nf);
                bean.setSnjzyszc(new BigDecimal(bean.getSnjzyszc()).add(new BigDecimal(snbxje)).toString());
                String bnbxje = getDfcz_tybxje("0", "0", "0", rs.getString("id"), rs.getString("dw"), nf);
                bean.setBnsryszc(new BigDecimal(bean.getBnsryszc()).add(new BigDecimal(bnbxje)).toString());
                bean.setHj3(String.valueOf(new BigDecimal(bean.getSnjzyszc()).add(new BigDecimal(bean.getBnsryszc()))));

                BigDecimal snzxl = new BigDecimal("0");
                if (new BigDecimal(bean.getSnjzysap()).compareTo(new BigDecimal("0"))>0)
                    snzxl = new BigDecimal(bean.getSnjzyszc()).divide(new BigDecimal(bean.getSnjzysap()), 2, BigDecimal.ROUND_HALF_UP);
                bean.setSnjzyszxl(new BigDecimal(bean.getSnjzyszxl()).add(snzxl).toString());

                BigDecimal bnzxl = new BigDecimal("0");
                if (new BigDecimal(bean.getBnsrysap()).compareTo(new BigDecimal("0"))>0)
                    bnzxl = new BigDecimal(bean.getBnsryszc()).divide(new BigDecimal(bean.getBnsrysap()), 2, BigDecimal.ROUND_HALF_UP);
                bean.setBnsryszxl(new BigDecimal(bean.getBnsryszxl()).add(bnzxl).toString());
                bean.setHj4(String.valueOf(snzxl.add(bnzxl)));
            }
            list.add(bean);
        }
        return list;
    }

    /**
     * 获取所有下级信息
     * @param id
     * @param jzField
     * @return
     */
    public List<BudgetSectionIncome2> recursionXj(String id,String jzField,String dw,String nf){
        RecordSet rs = new RecordSet();
        String sql = "select id,srzx,"+jzField+",yjsr,snjzhpje,yhpje,yqndyjjzje,yjsr from uf_zyczbksr_xj where sjzxsr = "+id+"";
        rs.executeQuery(sql);
        List<BudgetSectionIncome2> list = new ArrayList<>();
        while (rs.next()){
            BudgetSectionIncome2 bean = getBudgetSectionIncome2();
            bean.setKmjxj(1);
            bean.setDataid(rs.getString("id"));
            bean.setId(rs.getString("id"));

            List<BudgetSectionIncome2> xjList = recursionXj(rs.getString("id"), jzField,dw,nf);
            if (xjList.size()>0) {
                BigDecimal snjz = BigDecimal.ZERO;
                BigDecimal bnsr = BigDecimal.ZERO;
                BigDecimal snjzysap = BigDecimal.ZERO; //上年结转预算安排
                BigDecimal bnsrysap = BigDecimal.ZERO; //本年收入预算安排
                BigDecimal snjzwap = BigDecimal.ZERO; //上年结转未安排
                BigDecimal bnsrwap = BigDecimal.ZERO; //本年收入未安排
                BigDecimal snjzyszc = BigDecimal.ZERO; //上年结转预算支出
                BigDecimal bnsryszc = BigDecimal.ZERO; //本年收入预算支出
                BigDecimal snjzyszxl = BigDecimal.ZERO; //上年结转预算执行率
                BigDecimal bnsryszxl = BigDecimal.ZERO; //本年收入预算执行率
                for (BudgetSectionIncome2 beans:xjList){
                    snjz = snjz.add(new BigDecimal(beans.getSnjz()));
                    bnsr = bnsr.add(new BigDecimal(beans.getBnsr()));

                    snjzysap = snjzysap.add(new BigDecimal(beans.getSnjzysap()));
                    bnsrysap = bnsrysap.add(new BigDecimal(beans.getBnsrysap()));

                    snjzwap = snjzwap.add(new BigDecimal(beans.getSnjzwap()));
                    bnsrwap = bnsrwap.add(new BigDecimal(beans.getBnsrwap()));

                    snjzyszc = snjzyszc.add(new BigDecimal(beans.getSnjzyszc()));
                    bnsryszc = bnsryszc.add(new BigDecimal(beans.getBnsryszc()));

                    snjzyszxl = snjzyszxl.add(new BigDecimal(beans.getSnjzyszxl()));
                    bnsryszxl = bnsryszxl.add(new BigDecimal(beans.getBnsryszxl()));
                }
                bean.setSnjz(snjz.toString());
                bean.setBnsr(bnsr.toString());
                bean.setHj(snjz.add(bnsr).toString());

                bean.setSnjzysap(snjzysap.toString());
                bean.setBnsrysap(bnsrysap.toString());
                bean.setHj1(snjzysap.add(bnsrysap).toString());

                bean.setSnjzwap(snjzwap.toString());
                bean.setBnsrwap(bnsrwap.toString());
                bean.setHj2(snjzwap.add(bnsrwap).toString());

                bean.setSnjzyszc(snjzyszc.toString());
                bean.setBnsryszc(bnsryszc.toString());
                bean.setHj3(snjzyszc.add(bnsryszc).toString());

                bean.setSnjzyszxl(snjzyszxl.toString());
                bean.setBnsryszxl(bnsryszxl.toString());
                bean.setHj4(snjzyszxl.add(bnsryszxl).toString());

//                bean.setXj(xjList);
            }else{
                //已到最下级  取值
                bean.setSnjz(rs.getString(jzField));
                bean.setBnsr(rs.getString("yjsr"));
                bean.setHj(String.valueOf(new BigDecimal( rs.getString(jzField)).add(new BigDecimal(rs.getString("yjsr")))));

                bean.setSnjzysap(rs.getString("snjzhpje"));
                bean.setBnsrysap(rs.getString("yhpje"));
                bean.setHj1(String.valueOf(new BigDecimal(bean.getSnjzysap()).add(new BigDecimal(bean.getBnsrysap()))));

                bean.setSnjzwap(rs.getString("yqndyjjzje"));
                bean.setBnsrwap(rs.getString("yjsr"));
                bean.setHj2(String.valueOf(new BigDecimal(bean.getSnjzwap()).add(new BigDecimal(bean.getBnsrwap()))));

                String snbxje = getDfcz_tybxje("0", "1", "1", rs.getString("id"), dw, nf);
                bean.setSnjzyszc(snbxje);
                String bnbxje = getDfcz_tybxje("0", "0", "1", rs.getString("id"), dw, nf);
                bean.setBnsryszc(bnbxje);
                bean.setHj3(String.valueOf(new BigDecimal(bean.getSnjzyszc()).add(new BigDecimal(bean.getBnsryszc()))));

                BigDecimal snzxl = new BigDecimal("0");
                if (new BigDecimal(bean.getSnjzysap()).compareTo(new BigDecimal("0"))>0)
                    if (!bean.getSnjzysap().equals("0"))
                        snzxl = new BigDecimal(bean.getSnjzyszc()).divide(new BigDecimal(bean.getSnjzysap()), 2, BigDecimal.ROUND_HALF_UP);

                bean.setSnjzyszxl(snzxl.toString());

                BigDecimal bnzxl = new BigDecimal("0");
                if (new BigDecimal(bean.getBnsrysap()).compareTo(new BigDecimal("0"))>0)
                    bnzxl = new BigDecimal(bean.getBnsryszc()).divide(new BigDecimal(bean.getBnsrysap()), 2, BigDecimal.ROUND_HALF_UP);
                bean.setBnsryszxl(bnzxl.toString());
                bean.setHj4(String.valueOf(snzxl.add(bnzxl)));

            }
            list.add(bean);
        }
        return list;
    }

    /**
     * 项目经费 特殊处理
     * @param nf
     * @param jz
     * @param arr
     * @return
     */
    public BudgetSectionIncome2 getXmjfData(String nf,String jz,Object[] arr){
        BudgetSectionIncome2 bean = getBudgetSectionIncome2();
        String kmdm = arr[0].toString();//编号
        String kmName = arr[1].toString();//名称
        if (!kmdm.equals("2010902") && !kmdm.equals("2010909")){
            bean.setKmmc(kmName+"-"+arr[2].toString());
        }else{
            bean.setKmmc(kmName);
        }
        bean.setName(kmdm);
        //srzx 0:人员经费 1:公用经费 2:项目经费

        RecordSet rs = new RecordSet();
        String sql = "select * from (select id,substring(gnkmmc,2,9) gnkmmc,kmdm,"+jz+",yjsr,snjzhpje,yhpje,yqndyjjzje,dw from uf_zyczbksr_km where srnf = "+nf+")kmb where gnkmmc in ("+ arr[0].toString() +")";
        rs.executeQuery(sql);
        List<BudgetSectionIncomeApi2> listBean = new LinkedList<>();
        while (rs.next()){

            //接下来需要累加最末级金额
            List<BudgetSectionIncome2> xjList = getCzbksrEjData(rs.getString("id"), jz,rs.getString("dw"),nf,"");
            if (kmdm.equals("2010902") || kmdm.equals("2010909")){
                bean.setXj(xjList);
            }else if (xjList.size()>0){
                BigDecimal snjz = BigDecimal.ZERO;//上年结转
                BigDecimal bnsr = BigDecimal.ZERO;//本年收入
                BigDecimal snjzysap = BigDecimal.ZERO;//上年结转预算安排
                BigDecimal bnsrysap = BigDecimal.ZERO;//本年收入预算安排
                BigDecimal snjzwap = BigDecimal.ZERO; //上年结转未安排
                BigDecimal bnsrwap = BigDecimal.ZERO; //本年收入未安排
                BigDecimal snjzyszc = BigDecimal.ZERO; //上年结转预算支出
                BigDecimal bnsryszc = BigDecimal.ZERO; //本年收入预算支出
                BigDecimal snjzyszxl = BigDecimal.ZERO; //上年结转预算执行率
                BigDecimal bnsryszxl = BigDecimal.ZERO; //本年收入预算执行率
                for (BudgetSectionIncome2 beans:xjList){
                    snjz = snjz.add(new BigDecimal(beans.getSnjz()));
                    bnsr = bnsr.add(new BigDecimal(beans.getBnsr()));

                    snjzysap = snjzysap.add(new BigDecimal(beans.getSnjzysap()));
                    bnsrysap = bnsrysap.add(new BigDecimal(beans.getBnsrysap()));

                    snjzwap = snjzwap.add(new BigDecimal(beans.getSnjzwap()));
                    bnsrwap = bnsrwap.add(new BigDecimal(beans.getBnsrwap()));

                    snjzyszc = snjzyszc.add(new BigDecimal(beans.getSnjzyszc()));
                    bnsryszc = bnsryszc.add(new BigDecimal(beans.getBnsryszc()));

                    snjzyszxl = snjzyszxl.add(new BigDecimal(beans.getSnjzyszxl()));
                    bnsryszxl = bnsryszxl.add(new BigDecimal(beans.getBnsryszxl()));
                }
                bean.setSnjz(new BigDecimal(bean.getSnjz()).add(snjz).toString());
                bean.setBnsr(new BigDecimal(bean.getBnsr()).add(bnsr).toString());
                bean.setHj(String.valueOf(new BigDecimal(bean.getSnjz()).add(new BigDecimal(bean.getBnsr()))));

                bean.setSnjzysap(new BigDecimal(bean.getSnjzysap()).add(snjzysap).toString());
                bean.setBnsrysap(new BigDecimal(bean.getBnsrysap()).add(bnsrysap).toString());
                bean.setHj1(String.valueOf(new BigDecimal(bean.getSnjzysap()).add(new BigDecimal(bean.getBnsrysap()))));

                bean.setSnjzwap(new BigDecimal(bean.getSnjzwap()).add(snjzwap).toString());
                bean.setBnsrwap(new BigDecimal(bean.getBnsrwap()).add(bnsrwap).toString());
                bean.setHj2(String.valueOf(new BigDecimal(bean.getSnjzwap()).add(new BigDecimal(bean.getBnsrwap()))));

                bean.setSnjzyszc(new BigDecimal(bean.getSnjzyszc()).add(snjzyszc).toString());
                bean.setBnsryszc(new BigDecimal(bean.getBnsryszc()).add(bnsryszc).toString());
                bean.setHj3(String.valueOf(new BigDecimal(bean.getSnjzyszc()).add(new BigDecimal(bean.getBnsryszc()))));

                bean.setSnjzyszxl(new BigDecimal(bean.getSnjzyszxl()).add(snjzyszxl).toString());
                bean.setBnsryszxl(new BigDecimal(bean.getBnsryszxl()).add(bnsryszxl).toString());
                bean.setHj4(String.valueOf(new BigDecimal(bean.getSnjzyszxl()).add(new BigDecimal(bean.getBnsryszxl()))));
            }else{
                //已到最下级  取自身值
                String snjz = new BigDecimal(bean.getSnjz()).add(new BigDecimal(rs.getString(jz))).toString();
                bean.setSnjz(snjz);
                String yjsr = new BigDecimal(bean.getSnjz()).add(new BigDecimal(rs.getString("yjsr"))).toString();
                bean.setBnsr(yjsr);
                bean.setHj(String.valueOf(new BigDecimal(snjz).add(new BigDecimal(yjsr))));

                bean.setSnjzysap(new BigDecimal(bean.getSnjzysap()).add(new BigDecimal(rs.getString("snjzhpje"))).toString());
                bean.setBnsrysap(new BigDecimal(bean.getBnsrysap()).add(new BigDecimal(rs.getString("yhpje"))).toString());
                bean.setHj1(String.valueOf(new BigDecimal(bean.getSnjzysap()).add(new BigDecimal(bean.getBnsrysap()))));

                bean.setSnjzwap(new BigDecimal(bean.getSnjzwap()).add(new BigDecimal(rs.getString("yqndyjjzje"))).toString());
                bean.setBnsrwap(new BigDecimal(bean.getBnsrwap()).add(new BigDecimal(rs.getString("yjsr"))).toString());
                bean.setHj2(String.valueOf(new BigDecimal(bean.getSnjzwap()).add(new BigDecimal(bean.getBnsrwap()))));

                String snbxje = getDfcz_tybxje("0", "1", "0", rs.getString("id"), rs.getString("dw"), nf);
                bean.setSnjzyszc(new BigDecimal(bean.getSnjzyszc()).add(new BigDecimal(snbxje)).toString());
                String bnbxje = getDfcz_tybxje("0", "0", "0", rs.getString("id"), rs.getString("dw"), nf);
                bean.setBnsryszc(new BigDecimal(bean.getBnsryszc()).add(new BigDecimal(bnbxje)).toString());
                bean.setHj3(String.valueOf(new BigDecimal(bean.getSnjzyszc()).add(new BigDecimal(bean.getBnsryszc()))));

                BigDecimal snzxl = new BigDecimal("0");
                if (new BigDecimal(bean.getSnjzysap()).compareTo(new BigDecimal("0"))>0)
                    snzxl = new BigDecimal(bean.getSnjzyszc()).divide(new BigDecimal(bean.getSnjzysap()), 2, BigDecimal.ROUND_HALF_UP);
                bean.setSnjzyszxl(new BigDecimal(bean.getSnjzyszxl()).add(snzxl).toString());

                BigDecimal bnzxl = new BigDecimal("0");
                if (new BigDecimal(bean.getBnsrysap()).compareTo(new BigDecimal("0"))>0)
                    bnzxl = new BigDecimal(bean.getBnsryszc()).divide(new BigDecimal(bean.getBnsrysap()), 2, BigDecimal.ROUND_HALF_UP);
                bean.setBnsryszxl(new BigDecimal(bean.getBnsryszxl()).add(bnzxl).toString());
                bean.setHj4(String.valueOf(snzxl.add(bnzxl)));
            }
        }
        return bean;
    }




    /**
     * 获取地方财政同意报销金额
     * @param zcqd
     *          0和1 0为财政拨款 1为非财政拨款
     * @param ysly
     *          0和1 0为当年预算 1为上年结转
     * @param kmjxj
     *          0和1 分别对应科目级和科目下级，在财政收入的层级里0就是第一级，非第一级的都为1
     * @param dygnkmbid
     *          当kmjxj为0时为uf_zyfzbksr_km表的id，1则是uf_zyfzbksr_xj表的id
     * @param dw
     *          单位
     * @param nf
     *          年份
     * @return
     */
    public String getDfcz_tybxje(String zcqd,String ysly,String kmjxj,String dygnkmbid,String dw,String nf){
        RecordSet rs = new RecordSet();
        String sql = "select ISNULL(SUM(d1.tybxje-isnull(d1.cxje,0)),0) AS tybxje  from uf_bxjl_kj_dt1 where zcqd="+zcqd+" and ysly="+ysly+"  and kmjxj="+kmjxj+" and dygnkmbid='"+dygnkmbid+"' and ysxm in (select lcjlsqjlid from uf_jingfei_version where dlhsdw="+dw+" and ysnd="+nf+") ";
        rs.executeQuery(sql);
        if (rs.next()){
            if (rs.getString("tybxje") == null || rs.getString("tybxje").equals(""))
                return "0";
            else
                return rs.getString("tybxje");
        }else{
            return "0";
        }
    }

    private BudgetSectionIncome2 getBudgetSectionIncome2(){
        BudgetSectionIncome2 bean = new BudgetSectionIncome2();
        bean.setSnjz("0.00");
        bean.setBnsr("0.00");
        bean.setHj("0.00");

        bean.setSnjzysap("0.00");
        bean.setBnsrysap("0.00");
        bean.setHj1("0.00");

        bean.setSnjzwap("0.00");
        bean.setBnsrwap("0.00");
        bean.setHj2("0.00");

        bean.setSnjzyszc("0.00");
        bean.setBnsryszc("0.00");
        bean.setHj3("0.00");

        bean.setSnjzyszxl("0.00");
        bean.setBnsryszxl("0.00");
        bean.setHj4("0.00");

        return bean;
    }
}
